using System;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using RealState.BusinessObjects;

namespace RealState.DAL
{
	public class CitiesDAO
	{
		public CitiesDAO()
		{
			DbProviderHelper.GetConnection();
		}
		public List<Cities> GetCitiess()
		{
			try
			{
				List<Cities> lstCitiess = new List<Cities>();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECTCitiess",CommandType.StoredProcedure);
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					Cities oCities = new Cities();
					oCities.Id = Convert.ToInt32(oDbDataReader["Id"]);
					oCities.CityName = Convert.ToString(oDbDataReader["CityName"]);

					if(oDbDataReader["IsPublish"] != DBNull.Value)
						oCities.IsPublish = Convert.ToInt32(oDbDataReader["IsPublish"]);

					if(oDbDataReader["Sorted"] != DBNull.Value)
						oCities.Sorted = Convert.ToInt32(oDbDataReader["Sorted"]);
					lstCitiess.Add(oCities);
				}
				oDbDataReader.Close();
				return lstCitiess;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public Cities GetCities(int Id)
		{
			try
			{
				Cities oCities = new Cities();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECTCities",CommandType.StoredProcedure);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Id",DbType.Int32,Id));
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					oCities.Id = Convert.ToInt32(oDbDataReader["Id"]);
					oCities.CityName = Convert.ToString(oDbDataReader["CityName"]);

					if(oDbDataReader["IsPublish"] != DBNull.Value)
						oCities.IsPublish = Convert.ToInt32(oDbDataReader["IsPublish"]);

					if(oDbDataReader["Sorted"] != DBNull.Value)
						oCities.Sorted = Convert.ToInt32(oDbDataReader["Sorted"]);
				}
				oDbDataReader.Close();
				return oCities;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int AddCities(Cities oCities)
		{
			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERTCities",CommandType.StoredProcedure);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CityName",DbType.String,oCities.CityName));
				if (oCities.IsPublish.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsPublish",DbType.Int32,oCities.IsPublish));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsPublish",DbType.Int32,DBNull.Value));
				if (oCities.Sorted.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Sorted",DbType.Int32,oCities.Sorted));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Sorted",DbType.Int32,DBNull.Value));

				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int UpdateCities(Cities oCities)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("UPDATECities",CommandType.StoredProcedure);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CityName",DbType.String,oCities.CityName));
				if (oCities.IsPublish.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsPublish",DbType.Int32,oCities.IsPublish));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsPublish",DbType.Int32,DBNull.Value));
				if (oCities.Sorted.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Sorted",DbType.Int32,oCities.Sorted));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Sorted",DbType.Int32,DBNull.Value));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Id",DbType.Int32,oCities.Id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveCities(Cities oCities)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETECities",CommandType.StoredProcedure);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Id",DbType.Int32,oCities.Id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveCities(int Id)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETECities",CommandType.StoredProcedure);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Id",DbType.Int32,Id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
	}
}
